﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using Foundation;


namespace DataAccess {
    /// <summary>
    /// Implementierung des IDataAccess Interface für SQLite
    /// </summary>
    public class DataAccess : IDataAccess {
        /// <summary>
        /// Singleton Instanz
        /// </summary>
        private static IDataAccess instance;

        /// <summary>
        /// Methode, welche das Singleton Objekt der DataAccess zurück gibt
        /// </summary>
        /// <returns></returns>
        public static IDataAccess GetInstance() {
            if (instance == null) {
                instance = new DataAccess();
            }
            return instance; 
        }

        /// <summary>
        /// Connection zur Datenbank
        /// </summary>
        private SQLiteConnection connection; 

        /// <summary>
        /// privater Konstruktor, welcher für das Singleton Muster benötigt wird
        /// </summary>
        private DataAccess() {
            string dataPath = @"D:\privat\Projekte\rpgsoundmixer\rpg-soundmixer\data\rpgsoundmixer.db"; 
            
            this.connection = new SQLiteConnection();
            this.connection.ConnectionString = string.Format("Data Source={0};Version=3;", dataPath);
            this.connection.Open(); 
        }


        /// <summary>
        /// Gibt eine Liste mit allen Tracks zurück
        /// </summary>
        /// <returns></returns>
        public List<string> getTracks(){
            
            List<string> returnList = new List<string>(); 
            
            if (this.connection.State != System.Data.ConnectionState.Open) {
                this.connection.Open(); 
            }

            using(SQLiteCommand cmd = new SQLiteCommand()){
                cmd.CommandText = "SELECT * FROM track"; 
                
                using(SQLiteDataReader reader = cmd.ExecuteReader()){
                    while(reader.Read()){
                        returnList.Add(reader.GetString(1)); 
                    }
                }
            }
            return returnList; 
        }


        public void writeTracks(List<Track> tracks) {
            if (this.connection.State != System.Data.ConnectionState.Open) {
                this.connection.Open();
            }
            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "INSERT INTO track (idTrack, path, name) VALUES (@id, @path, @name)";
                cmd.Parameters.Add("@id", System.Data.DbType.String);
                cmd.Parameters.Add("@path", System.Data.DbType.String);
                cmd.Parameters.Add("@name", System.Data.DbType.String); 

                foreach (Track t in tracks) {
                    cmd.Parameters["@id"].Value = t.Id;
                    cmd.Parameters["@path"].Value = t.Path;
                    cmd.Parameters["@name"].Value = t.Name; 
                    cmd.ExecuteNonQuery();
                    
                    Console.WriteLine("Ech Liebe Dech");
                }
            } 
        }



        public void writeTrack(Track t) {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Gibt eine Liste mit allen Tracks in der Datenbank zurück
        /// </summary>
        /// <returns></returns>
        public List<Track> selectTracks() {
            List<Track> returnList = new List<Track>();

            using (SQLiteCommand cmd = new SQLiteCommand()) {
                cmd.CommandText = "SELECT idTrack, path, name FROM track";
                SQLiteDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    returnList.Add(new Track(reader.GetString(2), reader.GetString(1), reader.GetString(0)));
                }
                reader.Close(); 
            }

            return returnList;             
        }


        public List<Category> GetAllCategories() {
            List<Category> returnList = new List<Category>(); 

            
            using(SQLiteCommand cmd = this.connection.CreateCommand()){
                cmd.CommandText = "SELECT * FROM category";
                using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        returnList.Add(new Category(reader.GetGuid(0), reader.GetString(1)));
                    }
                }
            }

            return returnList; 
        }

        public void SaveNewCategory(Category value) {
            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "INSERT INTO category (idcategory, name) VALUES (@id, @category)";
                cmd.Parameters.Add("@id", System.Data.DbType.String).Value = value.Id;
                cmd.Parameters.Add("@category", System.Data.DbType.String).Value = value.Name;
                cmd.ExecuteNonQuery(); 
            }
        }


        public List<Track> GetUntaggedTracks() {
            List<Track> returnList = new List<Track>();


            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "SELECT * FROM track WHERE track.idtrack not in (SELECT trackId from trackInCategorie)";
                using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        returnList.Add(new Track(reader[2].ToString(), reader[1].ToString(), reader[0].ToString()));
                    }
                }
                return returnList;
            }
        }


        public void Save(Category c) {
            List<Guid> idList = new List<Guid>();

            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "SELECT trackId FROM trackInCategorie WHERE CategoryId = @categoryId";
                cmd.Parameters.Add("@categoryId", System.Data.DbType.String);
                using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                    

                    while (reader.Read()) {
                        idList.Add(reader.GetGuid(0));
                    }
                }
            }
            using(SQLiteCommand cmd = this.connection.CreateCommand()){
                cmd.CommandText = "INSERT INTO trackInCategorie (trackId, categoryId) VALUES (@trackId, @categoryId)";
                cmd.Parameters.Add("@trackId", System.Data.DbType.String);
                cmd.Parameters.Add("@categoryId", System.Data.DbType.String); 
                foreach (Track t in c.Tracklist) {
                    if(!idList.Contains(t.Id)){
                        cmd.Parameters["@trackId"].Value = t.Id;
                        cmd.Parameters["@categoryId"].Value = c.Id;
                        cmd.ExecuteNonQuery(); 
                    }
                }
            }                
        }


        public List<Track> GetTracksByCategory(Category cat) {
           List<Track> trackList = new List<Track>();
           using (SQLiteCommand cmd = this.connection.CreateCommand()) {
               cmd.CommandText = "SELECT * FROM track where track.idTrack in (SELECT trackId FROM trackInCategorie where CategoryId = @categoryId)";
               cmd.Parameters.Add("@categoryId", System.Data.DbType.String).Value = cat.Id;
               SQLiteDataReader reader = cmd.ExecuteReader();

               while (reader.Read()) {
                   trackList.Add(new Track(reader.GetString(2), reader.GetString(1), reader.GetString(0)));

               }
           }
           return trackList; 

        }

        /// <summary>
        /// ERstellt eine neue SoundSuite und speicher sie in der Datebank
        /// </summary>
        /// <param name="name"></param>
        public void CreateNewSoundSuite(string name) {
            SoundSuite suite = new SoundSuite(name);
            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "INSERT INTO SoundSuite (idSoundSuite, Name) VALUES (@id, @name)";
                cmd.Parameters.Add("@id", System.Data.DbType.String).Value = suite.Id;
                cmd.Parameters.Add("@name", System.Data.DbType.String).Value = suite.Name;
                cmd.ExecuteNonQuery();
            }
           
        }

       

        public List<SoundSuite> GetAllSoundSuites() {
            List<SoundSuite> suites = new List<SoundSuite>();
            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "SELECT * FROM SoundSuite";
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    SoundSuite suite = new SoundSuite(reader.GetString(1));
                    suite.Id = reader.GetGuid(0);
                    suites.Add(suite);
                }
            }
            return suites;
        }


        public void DeleteSuite(SoundSuite soundSuite) {
            using (SQLiteCommand cmd = connection.CreateCommand()) {
                cmd.CommandText = "DELETE FROM SoundSuite WHERE idSoundSuite = @id";
                cmd.Parameters.Add("@id", System.Data.DbType.String).Value = soundSuite.Id.ToString();
                cmd.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// Gitb ein Array mit allen EffectGroup anhand einer SoundSuite ID zurück
        /// </summary>
        /// <param name="guid">ID der SoundSuite</param>
        /// <returns>Eine Liste mit EffectGroups</returns>
        public object[] getEffektGroupsFromSoundSuiteID(Guid guid) {
            List<EffektGroup> returnList; 

            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "SELECT * FROM EffektGroup WHERE soundSuite = @suiteID";
                cmd.Parameters.Add("@suiteID", System.Data.DbType.String).Value = guid.ToString();
                using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                    returnList = GetEffektGrousFromReader(reader);
                }
            }
            return returnList.ToArray(); 
        }

        /// <summary>
        /// Gibt eine List mit den EffektGroups zurück, welche anhand der Daten in einem DataReader erstellt werden,
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static List<EffektGroup> GetEffektGrousFromReader(SQLiteDataReader reader) {
            List<EffektGroup> returnList = new List<EffektGroup>();             
            while (reader.Read()) {
                EffektGroup group = new EffektGroup();
                group.ID = reader.GetGuid(0);
                group.Key = reader.GetString(2).ToCharArray()[0];
                group.Name = reader.GetString(1);
                returnList.Add(group);
            }
            return returnList;
        }


        public SoundSuite GetSoundSuiteByID(Guid guid) {
            SoundSuite returnSuite;
            using (SQLiteCommand cmd = this.connection.CreateCommand()) {
                cmd.CommandText = "SELECT * FROM SoundSuite WHERE idSoundSuite = @id";
                cmd.Parameters.Add("@id", System.Data.DbType.String).Value = guid.ToString();
                using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                    reader.Read();
                    returnSuite = new SoundSuite(reader.GetString(1));
                    returnSuite.Id = reader.GetGuid(0);
                   // returnSuite.Abenteuer = reader.GetValue(2).GetType().Equals(typeof(System.DBNull)): );
                }
                cmd.CommandText = "SELECT * FROM EffektGroup WHERE SoundSuite = @id";
                cmd.Parameters.Add("@id", System.Data.DbType.String).Value = guid.ToString();
                using (SQLiteDataReader reader = cmd.ExecuteReader()) {
                    returnSuite.EffektGroups = DataAccess.GetEffektGrousFromReader(reader);
                }

            }
            return returnSuite; 
        }

        public string RPGSoundmixer { get; set; }


        public void AddNewEffektGroup(EffektGroup effektGroup, Guid soundSuite) {
            using (SQLiteCommand cmd = connection.CreateCommand()) {
                cmd.CommandText = "INSERT INTO `effektGroup` (`ideffektGroup`, `name`, `key`, `SoundSuite`) VALUES (@id, @name, @key, @soundSuiteID); ";
                cmd.Parameters.Add("@id", System.Data.DbType.String).Value = effektGroup.ID.ToString();
                cmd.Parameters.Add("@name", System.Data.DbType.String).Value = effektGroup.Name;
                cmd.Parameters.Add("@key", System.Data.DbType.String).Value = effektGroup.Key;
                cmd.Parameters.Add("@soundSuiteID", System.Data.DbType.String).Value = soundSuite.ToString();
                cmd.ExecuteNonQuery();
            }

        }
    }
}
